﻿Imports System.Data
Imports System.Data.SqlClient
Public Class ContasDAL
    Dim cmd As SqlCommand
#Region "Métodos"
    Function inserirConta(ByVal descricao As String, ByVal bancodigo As Integer, ByVal dtabertura As Date, ByVal dtfechamento As Nullable(Of Date), ByVal conagencia As Integer, ByVal ctcorrente As Integer, ByVal gerente As String, ByVal email As String, ByVal saldoinicial As Decimal, ByVal status As Integer, ByVal tipoConta As String) As Integer
        Dim cmd As SqlCommand
        Dim codigo As Integer
        Try
            cmd = New SqlCommand
            cmd.CommandText = "insert into Financeiro.Ofi_Contas(con_descricao,ban_codigo,con_dtabertura,con_dtfechamento,con_agencia,con_ctcorrente,con_gerente,con_email,con_saldoinicial,sta_codigo, con_tipo)" & _
              " values(@descricao,@bancodigo,@dtabertura,@dtfechamento,@conagencia,@ctcorrente,@gerente,@email,@saldoinicial,@status,@tipo)"
            ' "execute SPContas @codigo,@descricao,@bancodigo,@dtabertura,@dtfechamento,@conagencia,@ctcorrente,@gerente,@email,@saldoinicial,@status,@tipo"
            '    cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = codigo
            cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = descricao
            cmd.Parameters.Add(New SqlParameter("@bancodigo", SqlDbType.Int)).Value = bancodigo
            cmd.Parameters.Add(New SqlParameter("@dtabertura", SqlDbType.Date)).Value = dtabertura
            If (dtfechamento.HasValue) Then
                cmd.Parameters.Add(New SqlParameter("@dtfechamento", SqlDbType.Date)).Value = dtfechamento
            Else
                cmd.Parameters.Add(New SqlParameter("@dtfechamento", SqlDbType.Date)).Value = DBNull.Value
            End If
            cmd.Parameters.Add(New SqlParameter("@conagencia", SqlDbType.Int)).Value = conagencia
            cmd.Parameters.Add(New SqlParameter("@ctcorrente", SqlDbType.Int)).Value = ctcorrente
            cmd.Parameters.Add(New SqlParameter("@gerente", SqlDbType.VarChar)).Value = gerente
            cmd.Parameters.Add(New SqlParameter("@email", SqlDbType.VarChar)).Value = email
            cmd.Parameters.Add(New SqlParameter("@saldoinicial", SqlDbType.Decimal)).Value = saldoinicial
            cmd.Parameters.Add(New SqlParameter("@status", SqlDbType.Int)).Value = status
            cmd.Parameters.Add(New SqlParameter("@tipo", SqlDbType.VarChar)).Value = tipoConta
            'codigo = objconexao.executacmdAI(cmd, "Ofi_Contas")
            codigo = objconexao.executaCmd(cmd)
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return codigo
    End Function
    Function alterar(ByVal codigo As Integer, ByRef descricao As String, ByVal bancodigo As Integer, ByVal dtabertura As Date, ByVal dtfechamento As Nullable(Of Date), ByVal conagencia As Integer, ByVal ctcorrente As Integer, ByVal gerente As String, ByVal email As String, ByVal saldoinicial As Decimal, ByVal status As Integer, ByVal tipoConta As String)
        Dim cmd As SqlCommand
        Dim ret As Integer
        Try
            cmd = New SqlCommand
            cmd.CommandText = "execute SPContas @codigo,@descricao,@bancodigo,@dtabertura,@dtfechamento,@conagencia,@ctcorrente,@gerente,@email,@saldoinicial,@status,@tipoConta"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = codigo
            cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = descricao
            cmd.Parameters.Add(New SqlParameter("@bancodigo", SqlDbType.Int)).Value = bancodigo
            cmd.Parameters.Add(New SqlParameter("@dtabertura", SqlDbType.Date)).Value = dtabertura
            If (dtfechamento.HasValue) Then
                cmd.Parameters.Add(New SqlParameter("@dtfechamento", SqlDbType.Date)).Value = dtfechamento
            Else
                cmd.Parameters.Add(New SqlParameter("@dtfechamento", SqlDbType.Date)).Value = DBNull.Value
            End If
            cmd.Parameters.Add(New SqlParameter("@conagencia", SqlDbType.Int)).Value = conagencia
            cmd.Parameters.Add(New SqlParameter("@ctcorrente", SqlDbType.Int)).Value = ctcorrente
            cmd.Parameters.Add(New SqlParameter("@gerente", SqlDbType.VarChar)).Value = gerente
            cmd.Parameters.Add(New SqlParameter("@email", SqlDbType.VarChar)).Value = email
            cmd.Parameters.Add(New SqlParameter("@saldoinicial", SqlDbType.Decimal)).Value = saldoinicial
            cmd.Parameters.Add(New SqlParameter("@status", SqlDbType.Int)).Value = status
            cmd.Parameters.Add(New SqlParameter("@tipoConta", SqlDbType.VarChar)).Value = tipoConta
            ret = objconexao.executaCmd(cmd)
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return ret
    End Function
    Function Excluir(ByRef codigo As Integer) As Integer
        Dim cmd As SqlCommand
        Dim ret As Integer
        Try
            cmd = New SqlCommand
            cmd.CommandText = "update Financeiro.Ofi_Contas set sta_codigo=3 where con_codigo = @codigo"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = codigo
            ret = objconexao.executaCmd(cmd)
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return ret
    End Function
#End Region
#Region "Localizar por codigo"
    Function localizar(ByVal codigo As Integer) As DataTable 'Código
        Dim cmd As SqlCommand
        Dim tab As DataTable
        Try
            cmd = New SqlCommand
            cmd.CommandText = "select * from Financeiro.Ofi_Contas where con_codigo= @codigo"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = codigo
            tab = objconexao.executaConsulta(cmd)
            tab.DefaultView.AllowNew = False
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function
    'localiza na view a conta e o periordo
    Function localizaContaPeriodo(ByVal codconta As Integer, ByVal dtini As Date, ByVal dtfim As Date) As DataTable 'Geral
        Dim cmd As SqlCommand
        Dim tab As DataTable
        Try
            cmd = New SqlCommand
            cmd.CommandText = "execute SPOfiExtratoConta @conta,@dtini,@dtfim"
            cmd.Parameters.Add(New SqlParameter("@conta", SqlDbType.Int)).Value = codconta
            cmd.Parameters.Add(New SqlParameter("@dtini", SqlDbType.Date)).Value = Format(dtini, "yyyy/MM/dd")
            cmd.Parameters.Add(New SqlParameter("@dtfim", SqlDbType.Date)).Value = Format(dtfim, "yyyy/MM/dd")
            tab = objconexao.executaConsulta(cmd)
            tab.DefaultView.AllowNew = False
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function
    Function localizar() As DataTable 'Geral
        Dim cmd As SqlCommand
        Dim tab As DataTable
        Try
            cmd = New SqlCommand
            cmd.CommandText = "Select * from vw_FinContas where sta_codigo <> 3 order by con_descricao"
            tab = objconexao.executaConsulta(cmd)
            tab.DefaultView.AllowNew = False
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function
#End Region
#Region "Buscar Filtro"
    Public Function buscar(ByVal Descricao As String, tipo As Integer) As DataTable
        Dim tab = New DataTable
        Dim cmd As SqlCommand
        Try
            cmd = New SqlCommand
            Select Case tipo
                Case 0
                    cmd.CommandText = "select * from vw_FinContas where con_codigo = @codigo"
                    cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = Val(Descricao)
                Case 1
                    cmd.CommandText = "select * from vw_FinContas where con_descricao COLLATE SQL_Latin1_General_CP1_CI_AI like @descricao"
                    cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = "%" & Descricao & "%"
                Case 2
                    cmd.CommandText = "select * from vw_FinContas where ban_descricao COLLATE SQL_Latin1_General_CP1_CI_AI like @descricao"
                    cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = "%" & Descricao & "%"
                Case 3
                    cmd.CommandText = "select * from vw_FinContas where con_tipo = @descricao"
                    cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = Descricao
                Case 4
                    cmd.CommandText = "select * from vw_FinContas where sta_descricao COLLATE SQL_Latin1_General_CP1_CI_AI like @descricao"
                    cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = "%" & Descricao & "%"
                Case Else
                    cmd.CommandText = "select * from vw_FinContas where sta_codigo <> 3 order by con_descricao"
            End Select

            tab = objconexao.executaConsulta(cmd)
            tab.DefaultView.AllowNew = False
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function
#End Region
#Region "ValidaContas"
    Function validaContas(ByVal nome As String, ByVal bancocod As Integer, ByVal numagencia As Integer, ByVal numconta As Integer) As Boolean
        Dim cmd As SqlCommand
        Dim tab As DataTable
        Try
            cmd = New SqlCommand
            cmd.CommandText = "SELECT * FROM Financeiro.Ofi_Contas where con_descricao <> @nome and ban_codigo = @bancocod  and con_agencia = @numagencia and con_ctcorrente = @numconta "
            cmd.Parameters.Add(New SqlParameter("@nome", SqlDbType.VarChar)).Value = "%" & nome & "%"
            cmd.Parameters.Add(New SqlParameter("@bancocod", SqlDbType.Int)).Value = bancocod
            cmd.Parameters.Add(New SqlParameter("@numagencia", SqlDbType.Int)).Value = numagencia
            cmd.Parameters.Add(New SqlParameter("@numconta", SqlDbType.Int)).Value = numconta
            tab = objconexao.executaConsulta(cmd)
            If (tab.Rows.Count = 0) Then
                Return True
            Else
                Return False
            End If
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
    End Function
#End Region
#Region "Valida"
    Function validaContasNB(ByVal nome As String, ByVal bancocod As Integer) As Boolean
        Dim cmd As SqlCommand
        Dim tab As DataTable
        Try
            cmd = New SqlCommand
            cmd.CommandText = "SELECT * FROM Financeiro.Ofi_Contas where con_descricao <> @nome and ban_codigo = @bancocod"
            cmd.Parameters.Add(New SqlParameter("@nome", SqlDbType.VarChar)).Value = "%" & nome & "%"
            cmd.Parameters.Add(New SqlParameter("@bancocod", SqlDbType.Int)).Value = bancocod
            tab = objconexao.executaConsulta(cmd)
            If (tab.Rows.Count = 0) Then
                Return True
            Else
                Return False
            End If
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
    End Function
    Function validaAlteracao(ByVal descricao As String, ByVal concod As Integer) As Boolean
        Dim cmd As SqlCommand
        Dim tab As DataTable
        Try
            cmd = New SqlCommand
            cmd.CommandText = "SELECT * FROM Financeiro.Ofi_Contas where con_descricao = @descricao and con_codigo <> @concod and sta_codigo <> 3"
            cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = descricao
            cmd.Parameters.Add(New SqlParameter("@concod", SqlDbType.Int)).Value = concod
            tab = objconexao.executaConsulta(cmd)
            If (tab.Rows.Count = 0) Then
                Return True
            Else
                Return False
            End If
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
    End Function
#End Region
#Region "Fluxo de Caixa"
    Function fluxodecaixa(ByVal dtinicial As Date, ByVal dtfim As Date) As DataTable
        Dim cmd As SqlCommand
        Dim tab As DataTable
        Try
            cmd = New SqlCommand
            cmd.CommandText = "execute SPFluxodeCaixa @dtinicial,@dtfim"
            cmd.Parameters.Add(New SqlParameter("@dtinicial", SqlDbType.Date)).Value = dtinicial
            cmd.Parameters.Add(New SqlParameter("@dtfim", SqlDbType.Date)).Value = dtfim
            tab = objconexao.executaConsulta(cmd)
            tab.DefaultView.AllowNew = False
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function
#End Region
#Region "Valida Exclusão"
    Function validaexclusao(ByVal concod As Integer) As Boolean
        Dim cmd As SqlCommand
        Dim tab As DataTable
        Try
            cmd = New SqlCommand
            cmd.CommandText = "SELECT * FROM vw_FinContasPagar where con_codigo=@concodigo"
            cmd.Parameters.Add(New SqlParameter("@concodigo", SqlDbType.VarChar)).Value = concod
            tab = objconexao.executaConsulta(cmd)
            If (tab.Rows.Count = 0) Then
                Return True
            Else
                Return False
            End If
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
    End Function
    Function validaexclusao2(ByVal concod As Integer) As Boolean
        Dim cmd As SqlCommand
        Dim tab As DataTable
        Try
            cmd = New SqlCommand
            cmd.CommandText = "SELECT * FROM vw_FinContasReceber where con_codigo=@concodigo"
            cmd.Parameters.Add(New SqlParameter("@concodigo", SqlDbType.VarChar)).Value = concod
            tab = objconexao.executaConsulta(cmd)
            If (tab.Rows.Count = 0) Then
                Return True
            Else
                Return False
            End If
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
    End Function
#End Region
End Class
